#请将你的所有代码放在该py文件里。
#请使用相对路径读取“原始表格”下的文件，生成的新表格请放在与你的py作业同级目录下。
#在Python shell下，输出第(2), (3)小问的结果。


import pandas as pd
import numpy as np

data_list = ["公积金","四险", "医疗险"]
Df = pd.DataFrame()
fin_Df = []
ans_L = []

for year in range(2016,2020):
    for month in range(1, 13):
        str_year = str(year)
        str_mon  = str(month)
        if month < 10:
            str_mon = '0' + str_mon
        Df_L = []
        for name in data_list:
            fin_name = str_year + str_mon + name + '.xlsx'
            # print(fin_name)
            f = pd.read_excel("原始表格/{}".format(fin_name))
            df = pd.DataFrame(f)
            df["Time"] = year * 100 + month
            Df_L.append(df)

        for i in range(0, len(Df_L)):
            name_L = list(Df_L[i].columns)
            for j in range(0, len(name_L)):
                if "身份证" in name_L[j]:
                    name_L[j] = "身份证号"
            Df_L[i].columns = name_L

        Df = Df_L[0]
        for i in range(1, len(Df_L)):
            Df = pd.merge(Df, Df_L[i], how = "outer", on = "身份证号")
        Df.to_csv("temp.csv", encoding = "utf_8_sig")
        use_col = ["Time","身份证号","员工姓名","公积金","养老险", "失业险" , "工伤险" , "生育险" , "医疗险"]
        Df = Df[use_col]
        ans_L.append(Df)

ans = pd.concat(ans_L).reset_index(drop = True)
G = list(ans.columns)
G[0] = "年月"
G[1] = "员工身份证号"
ans.columns = G
ans.to_csv("五险一金汇总表.csv", encoding="utf_8_sig")

## total 

tot = 0
for i in range(3, 9):
    tot += ans[G[i]].sum()
print(tot)

## avg <= 1000
ans["year"] = ans["年月"]/100
ans["year"] = ans["year"].apply(lambda x: int(x))
ans1 = ans[ans["year"] == 2019].reset_index(drop = True)
ans2 = ans1.groupby("员工姓名")["公积金"].mean()
ans2 = dict(ans2)


# print(ans2)

for item in ans2.items():
    if item[1] < 1000:
        print(item[0])
